package bookshop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import bookshop.dao.CategoryDao;
import bookshop.domain.Category;
import bookshop.exception.DataAccessException;
import bookshop.service.helper.CategoryQueryHelper;
import bookshop.utils.DBUtils;

public class CategoryDaoJDBCImpl implements CategoryDao {

	private static final String SQL_ADD = "insert into category(ctg_name, ctg_book_account,ctg_create_time) values(?,0,?)";
	private static final String SQL_LOADALL = "select * from category order by ctg_id desc";
	private static final String SQL_GET_BYID = "select * from category where ctg_id=?";
	private static final String SQL_UPDATE = "update category set ctg_name=?,ctg_book_account=?,ctg_create_time=? where ctg_id=?";
	private static final String SQL_DEL = "delete from category where ctg_id=?";
	
	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.fzu.bookshop.dao.CategoryDao#addCategory(edu.fzu.bookshop.domain.
	 * Category)
	 */
	
	private String generateSQL(CategoryQueryHelper helper){
		
		String sql="select * from category where 1=1";
		
		if(helper.getQryCtgId()!=null)
			sql+=" and ctg_id like '%"+helper.getQryCtgId()+"%' ";
		
		if(helper.getQryCtgName()!=null)
			sql+=" and ctg_name like '%"+helper.getQryCtgName()+"%' ";
		
		sql+=" order by ctg_id";
		
		return sql;
		
	}
	
	public void addCategory(Category ctg) {

		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;

		try {
			ps = ct.prepareStatement(SQL_ADD);
			ps.setString(1, ctg.getCtgName());
			ps.setTimestamp(2, new java.sql.Timestamp(ctg.getCtgCreateTime()
					.getTime()));
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("图书类别信息保存失败，可能图书类别出现重复，请检查数据!");
		} finally {
			DBUtils.getInstance().releaseRes(ct, ps, null);
		}

	}

	public List<Category> loadall() {
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Category> ctgs = null;
		try {
			ps = ct.prepareStatement(SQL_LOADALL);
			rs = ps.executeQuery();
			while (rs.next()) {
				if (ctgs == null)
					ctgs = new ArrayList<Category>();
				Category ctg = new Category();

				ctg.setCtgId(rs.getInt(1));
				ctg.setCtgName(rs.getString(2));
				ctg.setCtgBookAccount(rs.getInt(3));
				ctg.setCtgCreateTime(new java.util.Date(rs.getTimestamp(4)
						.getTime()));

				ctgs.add(ctg);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}

		return ctgs;

	}

	public Category getCategoryById(int ctgId) {
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Category ctg = null;

		try {
			ps = ct.prepareStatement(SQL_GET_BYID);
			ps.setInt(1, ctgId);
			rs = ps.executeQuery();
			ctg = new Category();
			while (rs.next()) {
				ctg.setCtgId(rs.getInt(1));
				ctg.setCtgName(rs.getString(2));
				ctg.setCtgBookAccount(rs.getInt(3));
				ctg.setCtgCreateTime(new java.util.Date(rs.getTimestamp(4)
						.getTime()));

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}

		return ctg;

	}

	public void update(Category ctg) {
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;

		try {
			ps = ct.prepareStatement(SQL_UPDATE);
			ps.setString(1, ctg.getCtgName());
			ps.setInt(2, ctg.getCtgBookAccount());
			ps.setTimestamp(3, new java.sql.Timestamp(ctg.getCtgCreateTime()
					.getTime()));
			ps.setInt(4, ctg.getCtgId());
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {

			DBUtils.getInstance().releaseRes(ct, ps, null);
		}
	}

	public void delete(int ctgId) {
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;

		try {
			ps = ct.prepareStatement(SQL_DEL);
			ps.setInt(1, ctgId);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("仍然有属于该类别的图书存在，请先删除该类别下的图书信息在进行本操作");
		} finally {
			DBUtils.getInstance().releaseRes(ct, ps, null);
		}

	}

	
	public List<Category> getCategoryByCondition(CategoryQueryHelper helper) {
		String sql=this.generateSQL(helper);
		
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Category ctg=null;
		List<Category> ctgs=new ArrayList<Category>();
		
		try {
			ps=ct.prepareStatement(sql);
			rs=ps.executeQuery();
			
			while(rs.next()){
				
				ctg=new Category();
				ctg.setCtgId(rs.getInt("ctg_id"));
				ctg.setCtgName(rs.getString("ctg_name"));
				ctg.setCtgBookAccount(rs.getInt("ctg_book_account"));
				ctg.setCtgCreateTime(new java.util.Date(rs.getTimestamp(4).getTime()));
				
				ctgs.add(ctg);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("组合查询图书类别列表获取失败!");
		}finally{
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}
		
		return ctgs;
	}

	
	public Long countCategoryByConditon(CategoryQueryHelper helper) {
		String sql =this.generateSQL(helper);
		
		sql =sql.replace("*", "count(*) as total");
		sql =sql.substring(0,sql.indexOf("order by"));
		
		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Long count = 0L;
		
		try {
			ps=ct.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs.next()){
				count=rs.getLong("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}
		
		return count;
	}

	
	public List<Category> getScopedCategorys(CategoryQueryHelper helper,
			int startIndex, int fetchSize) {
		
		String sql = this.generateSQL(helper);
		sql += " limit " + startIndex + "," + fetchSize;

		Connection ct = DBUtils.getInstance().getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		List<Category> ctgs=new ArrayList<Category>();
		
		try {
			ps=ct.prepareStatement(sql);
			rs=ps.executeQuery();
			
			while(rs.next()){
				
				Category ctg=new Category();
				ctg.setCtgId(rs.getInt("ctg_id"));
				ctg.setCtgName(rs.getString("ctg_name"));
				ctg.setCtgBookAccount(rs.getInt("ctg_book_account"));
				ctg.setCtgCreateTime(new java.util.Date(rs.getTimestamp(4).getTime()));
				
				ctgs.add(ctg);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException("组合查询图书类别列表获取失败!");
		}finally{
			DBUtils.getInstance().releaseRes(ct, ps, rs);
		}
		
		return ctgs;
	}

	

}
